 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_rptMetric_AdvanceAccountInactiveDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_rptMetric_AdvanceAccountInactiveDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO   
/*
**************************************************************************
* Name:           csp_rptMetric_AdvanceAccountInactiveDetails
* Purpose:        Get data for report
* Usage:          csp_rptMetric_AdvanceAccountInactiveDetails 'week','12/28/2008','','12/28/2009','','','','','',41359 
* Change History:
* Date            By          Change
 05/01/2009       Ba973       Added New stored proc
**************************************************************************
*/ 
  
CREATE  PROCEDURE [dbo].[csp_rptMetric_AdvanceAccountInactiveDetails]     
(      
  @PeriodType VARCHAR(50)      
, @PeriodStartDate SMALLDATETIME      
, @Institution INT      
, @PeriodEndDate SMALLDATETIME = NULL   
, @Department INT = NULL    
, @Unit VARCHAR(250)  = NULL
, @PrincipalInvestigator VARCHAR(50)=NULL    
, @Sponsor  VARCHAR(50) = NULL    
, @FundNumber VARCHAR(50) = NULL -- no information yet   
, @DomainUserId INT =  NULL
)      
AS      
   
BEGIN   
SELECT  
aaw.PeriodType
,aaw.PeriodStartDate
,aaw.ProposalNumber
,aaw.ChiefCode
,aaw.ProposalStatus
,aaw.StatusDate
,aaw.ProjectNumber
,aaw.LastUpdatedBy
,aaw.PostAwardManager
,ai.Department AS Department --Table AgreementInfo
,ai.SponsorName AS Sponsor  --Table AgreementInfo
,ai.SponsorAgreementID AS SponsorID  --Table AgreementInfo
,ai.PrincipalInvestigator  AS PIName
,ai.Institution  --Table AgreementInfo  
FROM  dbo.rptMetric_AccountsInactivated  aaw
INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements (@DomainUserId) sec --updated on 1/28/09
			ON aaw.ProposalNumber = sec.FolderNumber
INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
			ON aaw.ProposalNumber = ai.FolderNumber
WHERE
			(ai.institutionid= @Institution  OR @Institution IS NULL OR @Institution = 0)
			AND (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 )
			AND (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit='0' OR @Unit='')
			AND  aaw.PeriodStartDate BETWEEN @PeriodStartDate AND @PeriodEndDate  
			AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0')    
			AND  (ai.Sponsorid = @Sponsor OR @Sponsor IS NULL OR @Sponsor='' OR @Sponsor='0')
			AND (ai.ProjectFundNumber IN (SELECT * FROM dbo.fnSplitFundNumbers(@FundNumber)) OR @FundNumber IS NULL OR @FundNumber='')
			AND aaw.PeriodType=@PeriodType  
 
  
END  

GO




